package data.userdata;

import java.rmi.RemoteException;
import java.rmi.server.UnicastRemoteObject;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import po.FacultyTeacherPO;
import po.Institute;
import dataservice.userdataservice.FacultyTeacherDataService;

public class FacultyTeacherData extends UnicastRemoteObject implements
		FacultyTeacherDataService {

	PreparedStatement ps = null;
	Connection ct = null;
	ResultSet rs = null;

	public FacultyTeacherData() throws RemoteException {
	}

	@Override
	public void init() {
		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			ct = DriverManager.getConnection("jdbc:odbc:CourseSelectionSystem");
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	@Override
	public void finish() {
		try {
			if (ps != null) {
				ps.close();
			}
			if (ct != null) {
				ct.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	@SuppressWarnings("finally")
	@Override
	public boolean insert(FacultyTeacherPO po) {
		init();
		try {
			String id = po.getId();
			String name = po.getName();
			String password = po.getPassword();
			String institute = po.getInstitute().toString();
			String sql = "select id from FacultyTeacher where id='" + id + "'";
			ps = ct.prepareStatement(sql);
			rs = ps.executeQuery();
			if (rs.next()) {
				if (rs != null) {
					rs.close();
				}
				finish();
				return false;
			}
			if (rs != null) {
				rs.close();
			}
			sql = "insert into FacultyTeacher values('" + id + "','" + name
					+ "','" + password + "','" + institute + "')";
			ps = ct.prepareStatement(sql);
			ps.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			finish();
		}
		return true;

	}

	@Override
	public boolean delete(String id) {
		init();
		try {
			String sql = "delete from FacultyTeacher where id='" + id + "'";
			ps = ct.prepareStatement(sql);
			int i = ps.executeUpdate();
			finish();
			if (i == 1) {
				return true;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	@Override
	public boolean update(FacultyTeacherPO po) {
		init();
		try {
			String sql = "update FacultyTeacher set name='" + po.getName()
					+ "', password='" + po.getPassword() + "', institute='"
					+ po.getInstitute().toString() + "' where id='"
					+ po.getId() + "'";
			ps = ct.prepareStatement(sql);
			int i = ps.executeUpdate();
			finish();
			if (i == 1) {
				return true;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	@Override
	public FacultyTeacherPO find(String id) {
		init();
		FacultyTeacherPO po = null;

		try {
			String sql = "select * from FacultyTeacher where id='" + id + "'";
			ps = ct.prepareStatement(sql);
			rs = ps.executeQuery();
			if (rs.next()) {
				po = new FacultyTeacherPO(rs.getString(1), rs.getString(2),
						rs.getString(3), Institute.toEnum(rs.getString(4)));
			}
			if (rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			return null;
		}
		finish();
		return po;
	}

	@Override
	public ArrayList<FacultyTeacherPO> find(Institute institute) {
		ArrayList<FacultyTeacherPO> list = new ArrayList<FacultyTeacherPO>();
		init();
		try {
			String sql = "select * from FacultyTeacher where institute='"
					+ institute + "' order by id";
			ps = ct.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				FacultyTeacherPO po = new FacultyTeacherPO(rs.getString(1),
						rs.getString(2), rs.getString(3), Institute.toEnum(rs
								.getString(4)));
				list.add(po);
			}
			if (rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			return null;
		}
		finish();
		return list;
	}

}
